package com.zhuangjie.generator.test.task2.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.zhuangjie.generator.test.task2.entity.TOrder;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

public class TOrderAnalysisEventListener extends AnalysisEventListener<TOrder> {
    @Override
    public void invoke(TOrder torder, AnalysisContext analysisContext) {
        // 逐行处理
        System.out.println(torder);
        // 保存到数据库
        saveToDB(torder);

    }

    private void saveToDB(TOrder torder) {
        Connection connection=null;
        PreparedStatement ps=null;

        try {
            //1.register
            Class.forName("com.mysql.jdbc.Driver");

            //2.getConnection
            String url="jdbc:mysql://localhost:3306/seata";
            Properties info=new Properties();
            info.put("user", "root");
            info.put("password", "3333");
            connection= DriverManager.getConnection(url, info);

            //3.create Statement
            String sql="insert into t_order (orderid,orderdate,qty) values(?,?,?)";
            ps=connection.prepareStatement(sql);
            ps.setString(1, torder.getOrderid());
            ps.setString(2, torder.getOrderdate().toString());
            ps.setInt(3, torder.getQty());

            //4.excuteUpdate
            int resultSet=ps.executeUpdate();
            if(resultSet>0){
                //如果插入成功，则打印success
                System.out.println("Sucess");
            }else{
                //如果插入失败，则打印Failure
                System.out.println("Failure");
            }

        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            //5.关闭资源
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(ps!=null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}
